SQL常用优化手法

1.创建必要的索引

   在经常检索的字段上创建索引，创建索引会给检索带来巨大的性能提升，因此在发现检索速度过慢的时候应该首先想到就是创建索引。

 

2.使用预编译查询

   程序通常根据用户输入动态执行SQL语句，这个时候应该尽量使用参数化SQL，这样不仅可以避免SQL注入漏洞攻击，最重要的事数据库会对这些参数化SQL执行预编译。这样，第一次执行的时候DBMS会为这个SQL语句进行查询优化并且执行预编译，以后再执行这个SQL的时候就直接使用预编译的结果，这样可以大大的提高执行的速度。

 

3.调整Where子句中的连接顺序

    DBMS一般采用自下而上的顺序解析Where子句，根据这个原理，表连接最好写在其他的Where条件之前，这样就可以过滤掉最大的数据记录。

    如下面的SQL性能较差：

     select * from T_Person

      Where FSalary > 50000

      AND FPosition = 'Manager'

      AND 25 < (select  count（*）from T_Manager where ID = T_Person.FmanagerID)

  我们将子句移到前面，这样的SQL性能比较好：

     select * from T_Person

      Where 25 < (select  count（*）from T_Manager where ID = T_Person.FmanagerID)

      AND  FSalary > 50000

      AND FPosition = 'Manager'

     

4.Select 语句中避免使用*

   “Select *” 比较简单，但是除非真的需要检索所有的列，否则这样将检索出不需要的列，增加网络负载和服务器的资源消耗。即使确实需要检索所有的列，也不要使用select *，因为这个是一个非常低效的方法，DBMS在解析的过程中，会将* 依次转换成所有的列名，这意味着将耗费更多的时间。

 

5.尽量将多条SQL语句压缩到一句SQL中

   每次执行SQL的时候都要建立网络连接，进行权限校验，进行SQL语句的查询优化，发送执行结果，这个过程是很耗时的，因此应该尽量避免过多的执行SQL语句，能够压缩到一句SQL执行的语句就不要用多条来执行。

 

6.用Where子句中替换Having子句

   要避免使用Having子句，因为Having只会在检索出所有的记录之后才对结果集进行过滤。如果能通过Where子句限制记录的数目，那就能减少这方面的开销。Having中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在Where子句中。

 

7.使用表的别名

   当在SQL语句中有连接多个表的时候，请使用表的别名并把别名的前缀置于每个列名上，这样就可以减少解析的时间并减少那些有列名歧义引起的语法错误。

 

8. 用EXISTS 替代IN

    在查询中，为了满足一个条件，往往需要对另一个表进行连接，在这种情况下，使用EXISTS而不是使用IN，通常将提高查询效率，因为IN子句将执行一个子查询内部的排序和合并。下面的语句2就比语句1效率更高。

   语句1：

     select * from T_Employee

     where FNumber > 0 and FDEPTNO in (select FNumber from T_Department where FmanagerName = 'Tom')

 

  语句2：

   select * from T_Employee where FNumber > 0

and EXIESTS (select 1 from T_Department were T_Department.ID = T_Empoyee.FDEPTNO  and Fmanagement = 'Tom')

 ( ************************       子查询表大的用exists，子查询表小的用in    *************************************)

9.用表连接替换EXISTS

   通常来说，表连接方式比EXISTS更有效率，下面的语句比上面的语句2 效率更高

   select T_Employee.* from T_Employee ,T_Department d

  where T_Employee.FNumber > 0 and T_Department.ID = T_Empoyee.FDEPTNO  and d.Fmanagement = 'Tom'

 

10.避免在索引列上使用计算

   在Where子句中，如果索引列是计算或者函数的一部分，DBMS优化器将不会使用索引而使用全表扫描。

 

 11.用UNION ALL替换UNION

     当SQL语句需要UNION两个查询结果集合的时候，即使检索结果中不会有重复的记录，使用UNION这两个结果集同样会尝试合并结果，然后输出最终结果前进行排序。因此，如果检索结果中不会有重复记录的话，应该用UNION ALL替换UNION，这样效率更高。

 

 12.避免隐式类型转换造成全表扫描

      Select FID，FAGE，FNAME from T_Person where FAGE =10 （注意“FAGE为String类型的）

在这个SQL语句中，将字符串类型字段FLevel与数值10 进行比较，由于大部分数据库的隐式转换类型中数值类型的优先级高于字符串类型，因此DBMS会对FAGE字段进行隐式类型转换，相当于执行：

       Select FID，FAGE，FNAME from T_Person where TO_INT（FAGE） =10

  所以应该这样写：  Select FID，FAGE，FNAME from T_Person where FAGE = ’10‘

 

 

13.防止检索范围过宽

   如果DBMS优化器认为检索范围过宽，那么它将放弃索引查找而使用全表扫描，下面可能造成检索范围过宽的情况：

   （1）使用IS NOT NULL或者不等于判断，可能造成优化器假设匹配的记录数太多。

   （2）使用LIEK的时候，"a%"将会使用索引，而"a%c" 和 "%c" 则会使用全表扫描，因此"a%c" 和 "%c" 不能被有效的评估匹配的数量。